<?php
namespace Crm\Table;

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Expression;

class CatalogTable extends AbstractTable
{

    protected $table = 'hy_catalog';

    protected $id = 'catalog_id';

    public function find($query = array())
    {
        $select = new Select(array(
            'o' => $this->getTable()
        ));
        $select->columns(array(
            'catalog_id',
            'catalog_name'
        ));
        if (! empty($query['catalog_name'])) {
            $spec = function (Where $where) use($query) {
                $where->like('o.catalog_name', '%'.$query['catalog_name'] . "%");
            };
            
            $select->where($spec);
        }
        $select->order('o.catalog_id DESC');
        
        return $this->fetchPaginate($select, $query);
    }
    
    public function findAll()
    {
        $rowset = $this->select();
        $result = $rowset->toArray();
        return $result;
    }

    public function findOne($id)
    {
        $rowset = $this->select(array(
            $this->getId() => $id
        ));
        $row = $rowset->current();
        return $row;
    }

    public function create($data)
    {
        return $this->insert($data);
    }

    public function updateById($data, $id)
    {
        return $this->update($data, array(
            $this->getId() => $id
        ));
    }

    public function remove($id)
    {
        return $this->delete(array(
            $this->getId() => $id
        ));
    }

    public function deleteItem($id)
    {
        if (empty($id)) {
            throw new \Exception("id field is invalid!");
        }
        return $this->delete(array(
            'catalog_id' => $id
        ));
    }

    public function getItem($id)
    {
        if (empty($id)) {
            throw new \Exception("id field is invalid!");
        }
        $sql = 'SELECT * FROM `hy_catalog` WHERE `catalog_id` =:catalog_id';
        $statement = $this->getAdapter()->query($sql);
        $results = $statement->execute(array(
            ':catalog_id' => $id
        ));
        $row = $results->current();
        return $row;
    }
 
	public function updateItem($id,$data){
	    if(empty($id)){
	        throw new \Exception("id field is invalid!");
	    }
	    $data = array(
	        'city_id'=>$data['city_id'],
	        'catalog_name'=>$data['catalog_name'],
	        'catalog_description'=>$data['catalog_description'],
	        'update_time'=>new Expression('NOW()'),
	    );
	    return $this->update($data,array('catalog_id'=>$id));
	}
	
	
	public function addItem($data){
	    $data = array(
	        'city_id'=>$data['city_id'],
	        'catalog_name'=>$data['catalog_name'],
	        'catalog_description'=>$data['catalog_description'],
	        'update_time'=>new Expression('NOW()'),
	        'create_time'=>new Expression('NOW()'),
	    );
	
	    $this->insert($data);
	    $id = $this->lastInsertValue;
	    return $id;
	}
	
	public function getCatalogList($query = array())
	{
	    $select = new Select(array('c' =>$this->getTable()));
	    $select->join(
	        array('t' => 'hy_city'),
	        't.city_id = c.city_id',
	        array('city_name') ,
	        $select::JOIN_LEFT
	        );
	    if(!empty($query['city_id'])) {
	        $select->where(array('c.city_id' => $query['city_id']));
	    }
	    $select->order('c.catalog_id');
	
	    return $this->fetchPaginate($select,$query);
	}
}